Declare @m_TableId int
Declare @m_TableName Varchar(255)
Declare @m_ColName varchar(500)
Declare @m_ColDataType Varchar(20)
Declare @m_ColDatalength int
Declare @m_ColId int
Declare @m_PK Varchar(500)
Declare @m_flag int
Declare @m_i int
Declare @m_j int
Declare @m_PKFlag int


Declare @m_TabNamea Varchar(100)
Declare @m_ColNamea Varchar(100)
Declare @m_ColDataTypea Varchar(20)
Declare @m_ColLengtha Varchar(6)
Declare @m_k int

Declare @m_Statement Varchar(5000)

Declare Newtables_Cur  CURSOR STATIC FOR
select name, id  from sysobjects where xtype = 'U'
and name not in (select name from PTrackDB0421.dbo.sysobjects)

Open Newtables_Cur

/*Print Convert(Varchar(10),@@CURSOR_ROWS)*/
set @m_i=@@CURSOR_ROWS
While @m_i>0

Begin
  set @m_i=@m_i-1
  Fetch Next From Newtables_Cur Into @m_TableName, @m_TableId

  
  Declare NewCols_Cur CURSOR  STATIC FOR
  select A.name, A.length, A.colid, B.name from syscolumns A, systypes B  where A.id=@m_TableId and    B.xtype=A.xtype
  
  Open NewCols_Cur
  
  Select @m_PK='PRIMARY KEY ('
  Set  @m_flag=0
  Set @m_PKFlag=0
  
  Print 'Create Table ' + @m_TableName+ '(' 
  
  set @m_j=@@CURSOR_ROWS
  While @m_j>0
  Begin
 set @m_j=@m_j-1 
 
        Fetch Next From NewCols_Cur into @m_ColName, @m_ColDataLength, @m_ColId, @m_ColDataType
 IF (Select count(*) from sysindexkeys where colid=  @m_ColId and id=@m_TableId and keyno=colid)>0
 Begin
  Set @m_PKFlag=1
  IF @m_ColDataType<>'varchar'and @m_ColDataType<>'char'
   Begin
    /*PRINT @m_ColName+'  '+@m_ColDataType+' NOT NULL,'     */
    Set @m_Statement=@m_ColName+'  '+@m_ColDataType+' NOT NULL'
   End
  Else
   Begin
    /*PRINT @m_ColName+'  '+@m_ColDataType+ '  ('+Convert(varchar(10),@m_ColDataLength) + ')   '+' NOT NULL,' */
    Set @m_Statement=@m_ColName+'  '+@m_ColDataType+ '  ('+Convert(varchar(10),@m_ColDataLength) + ')   '+' NOT NULL'
   End
  if @m_flag=0
  Begin
    Select @m_PK=@m_PK+ ' ' + @m_ColName
   set @m_flag=1
  End
  Else
  Begin
   Select @m_PK=@m_PK+ ' ,  ' + @m_ColName
  End
  
 End
 Else
 Begin
  IF @m_ColDataType<>'varchar'and @m_ColDataType<>'char'
   Begin
    /*PRINT @m_ColName+'  '+@m_ColDataType+'  NULL,'     */
    Set @m_Statement=@m_ColName+'  '+@m_ColDataType+'  NULL'
   End
  Else
   Begin
    /*PRINT @m_ColName+'  '+@m_ColDataType+ '  ('+Convert(varchar(10),@m_ColDataLength) + ')   '+'  NULL,' */
    Set @m_Statement=@m_ColName+'  '+@m_ColDataType+ '  ('+Convert(varchar(10),@m_ColDataLength) + ')   '+'  NULL'
   End

  
 End

 IF @m_j=0  /*last column*/
 Begin
  IF @m_PKFlag=1 /*has the primary key*/
  Begin
   Print @m_Statement+',' + @m_PK+ ' ))'
  End
  Else
  Begin
   Print @m_Statement+')'
  End 
 End 
 Else /* not the last one*/
 Begin
  Print @m_Statement+','
 End
 
  

End
 
  Close NewCols_Cur
  Deallocate NewCols_Cur

/*  Select @m_PK=@m_PK+ ' ))'
  Print @m_PK*/

  Print 'Grant All On ' + @m_TableName + ' TO PTAdmin'
  Print ' '
  Print ' '

End

Close NewTables_Cur
Deallocate NewTables_Cur

/*generate the existing table coloum alter*/


Declare AlterCols_Cur CURSOR  STATIC FOR
  select A.name, B.name, Left(Cast(C.name as VarChar),20), Convert(varchar(6),B.length)
from sysobjects A, syscolumns B, systypes C
where A.id = B.id
and A.xtype = 'U' and B.xtype=C.xtype
and A.name +'.' + B.name not in
(select A.name +'.' + B.name from PTrackDB0421.dbo.sysobjects A, PTrackDB0421.dbo.syscolumns B
where A.id = B.id
and A.xtype = 'U') and A.name in ( select name from PTrackDB0421.dbo.sysobjects where xtype = 'U')

Open AlterCols_Cur
set @m_k=@@CURSOR_ROWS
While @m_k>0
 Begin
  set @m_k=@m_k-1
  Fetch Next From AlterCols_Cur into @m_TabNamea, @m_ColNamea, @m_ColDataTypea, @m_ColLengtha
  IF @m_ColDataTypea<>'varchar'and  @m_ColDataTypea<>'char'
  Begin
   Print 'ALTER TABLE ' + @m_TabNamea + ' ADD' + ' ' + @m_ColNamea + ' ' + @m_ColDataTypea + ' NULL'
  End
  Else
  Begin
   Print 'ALTER TABLE ' + @m_TabNamea + ' ADD' + ' ' + @m_ColNamea + ' ' + @m_ColDataTypea + ' ('+ @m_ColLengtha + ') ' + ' NULL'
  End
  
End

Close AlterCols_Cur
Deallocate AlterCols_Cur

/*Print  'UPDATE LogIn SET IsActiveDevTrackUser = 1 where IsActivePerson<>0'*/
/*Print  'UPDATE LogIn SET IsActiveDevTrackUser = 0 where IsActivePerson=0'*/


declare @m_Version int

select @m_Version=version from SystemSettings
Print 'Update SystemSettings set version=' + Convert(varchar(4),@m_Version)
Print 'Go'


Go

